from datetime import datetime
import os

import openpyxl
from fastapi import APIRouter
import pandas as pd
from pydantic import BaseModel

from routes import sqlite
from util.common import FileHandler

scan_app = APIRouter()


# 导入数据
@scan_app.get("/importdata")
async def importdata():
    path = await FileHandler.select_file("请选择要扫描的excel文件")
    if path == "":
        return {
            "code": 204,
            "status": "no",
            "message": "没有选择文件",
        }

    # 清空数据库
    if not sqlite.clear_table():
        return {
            "code": 204,
            "status": "no",
            "message": "清空数据库失败",
        }

    df = pd.read_excel(path, sheet_name='Sheet1', usecols=['编码', '条码', '库存货位', '库存量', '是否已扫描'])
    if df.empty:
        return {
            "code": 204,
            "status": "no",
            "message": "获取数据为空，导入失败",
        }
    df.rename(columns={'编码': 'item', '条码': 'sn', '库存货位': 'hw', '库存量': 'qty', '是否已扫描': 'isscan'},
              inplace=True)

    if sqlite.insert_dataframe(df):
        return {
            "code": 200,
            "status": "ok",
            "message": "success",
        }
    return {
        "code": 204,
        "status": "no",
        "message": "导入失败",
    }


# 根据货位查询库存

@scan_app.get("/hw")
async def hw(hw: str):
    if hw == "":
        return {
            "code": 204,
            "status": "no",
            "message": "没有输入货位",
            "data": []
        }
    df = sqlite.query(f"SELECT item,sn,hw,qty,isscan FROM scan WHERE hw='{hw}'")
    if df.empty:
        return {
            "code": 204,
            "status": "no",
            "message": f"{hw}:没有数据",
            "data": []
        }
    dflist = df.to_dict('records')
    return {
        "code": 200,
        "status": "ok",
        "message": "success",
        "data": dflist,
    }


class updateType(BaseModel):
    sn: str


@scan_app.put("/updateisscao")
async def updateisscao(data: updateType):
    if data.sn == "":
        return {
            "code": 204,
            "status": "no",
            "message": "没有输入条码"
        }
    sql = f"UPDATE scan SET isscan=1 WHERE sn='{data.sn}'"
    if sqlite.update_isscan(sql):
        return {
            "code": 200,
            "status": "ok",
            "message": "success",
        }
    return {
        "code": 204,
        "status": "no",
        "message": "更新失败"
    }


@scan_app.get("/alldata")
async def alldata():
    df = sqlite.query("SELECT item,sn,hw,qty,isscan FROM scan")
    if df.empty:
        return {
            "code": 204,
            "status": "no",
            "message": "没有数据",
        }
    df.rename(columns={'item': '编码', 'sn': '条码', 'hw': '库存货位', 'qty': '库存量', 'isscan': '是否已扫描'},
              inplace=True)
    df.to_excel(f"data_{datetime.now().strftime('%Y-%m-%d %H%M%S')}.xlsx", index=False)
    return {
        "code": 200,
        "status": "ok",
        "message": "success",
    }


# 批量更改

class updateType_batch(BaseModel):
    sn: list[str]


@scan_app.put("/batchupdate")
async def batchupdate(data: updateType_batch):
    if len(data.sn) == 0:
        return {
            "code": 204,
            "status": "no",
            "message": "没有输入条码"
        }
    sqllist = []
    for i in data.sn:
        sqllist.append(f"UPDATE scan SET isscan=1 WHERE sn='{i}'")
    if sqlite.update_isscan_batch(sqllist):
        return {
            "code": 200,
            "status": "ok",
            "message": "success",
        }
    return {
        "code": 204,
        "status": "no",
        "message": "更新失败"
    }


def is_file_locked(file_path):
    if not os.path.isfile(file_path):
        return False
    try:
        with open(file_path, 'r+'):
            return False
    except IOError:
        return True
# 向异常表中写入数据
class abnormalType(BaseModel):
    hw: str
    sn: list[str]


@scan_app.post("/abnormal")
async def abnormal(data: abnormalType):
    if data.hw == "" or data.sn == "":
        return {
            "code": 204,
            "status": "no",
            "message": "没有输入数据"
        }
    try:
        # 拼接得到文件的完整路径
        file_path = os.path.join(os.getcwd(), '异常表.xlsx')
        # 检查文件是否被锁定
        if is_file_locked(file_path):
            return {
                "code": 208,
                "status": "no",
                "message": "文件被锁定，无法写入"
            }
        # 打开Excel文件
        workbook = openpyxl.load_workbook(file_path)

        # 选择要操作的工作表Sheet1
        sheet = workbook['Sheet1']
        # 获取A列最后一行非空单元格的行号
        last_row = sheet.max_row + 1

        # 写入数据，例如在A1单元格写入"Hello"，B1单元格写入"World"
        for i, sn in enumerate(data.sn):
            sheet.cell(row=last_row + i, column=1, value=datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
            sheet.cell(row=last_row + i, column=2, value=data.hw)
            sheet.cell(row=last_row + i, column=3, value=sn)
        # 保存更改
        workbook.save(file_path)
        # 关闭工作簿
        workbook.close()
        return {
            "code": 200,
            "status": "ok",
            "message": "数据已写入异常表"
        }
    except Exception as e:
        return {
            "code": 204,
            "status": "no",
            "message": "写入异常表失败"
        }


